﻿create PROCEDURE Sync.SP_LoanFeasabilityStudy_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanFeasabilityStudy_Temp TABLE 
		(
			LoanFeasabilityStudyID UniqueIdentifier,
			[LoanID] uniqueidentifier,
				[FeasabilityStudyDate] datetime,
				[RevenueAmount] float,
				[CostAmount] float,
				[ProfitAmount] float,
				[ReturnOnInvestmentPercent] decimal,
				[LoanAmount] float,
				[LoanPaymentPeriod] int,
				[LoanPaymentMethodID] uniqueidentifier,
				[Notes] nvarchar(MAX),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanFeasabilityStudy_Temp 
	   SELECT	Tbl.Col.value('LoanFeasabilityStudyID[1]','uniqueidentifier') LoanFeasabilityStudyID,
                Tbl.Col.value('LoanID[1]', 'uniqueidentifier') [LoanID],
Tbl.Col.value('FeasabilityStudyDate[1]', 'datetime') [FeasabilityStudyDate],
Tbl.Col.value('RevenueAmount[1]', 'float') [RevenueAmount],
Tbl.Col.value('CostAmount[1]', 'float') [CostAmount],
Tbl.Col.value('ProfitAmount[1]', 'float') [ProfitAmount],
Tbl.Col.value('ReturnOnInvestmentPercent[1]', 'decimal') [ReturnOnInvestmentPercent],
Tbl.Col.value('LoanAmount[1]', 'float') [LoanAmount],
Tbl.Col.value('LoanPaymentPeriod[1]', 'int') [LoanPaymentPeriod],
Tbl.Col.value('LoanPaymentMethodID[1]', 'uniqueidentifier') [LoanPaymentMethodID],
Tbl.Col.value('Notes[1]', 'nvarchar(MAX)') [Notes],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanFeasabilityStudy') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanFeasabilityStudy] ([LoanFeasabilityStudyID],[LoanID],[FeasabilityStudyDate],[RevenueAmount],[CostAmount],[ProfitAmount],[ReturnOnInvestmentPercent],[LoanAmount],[LoanPaymentPeriod],[LoanPaymentMethodID],[Notes])
	SELECT t.[LoanFeasabilityStudyID],t.[LoanID],t.[FeasabilityStudyDate],t.[RevenueAmount],t.[CostAmount],t.[ProfitAmount],t.[ReturnOnInvestmentPercent],t.[LoanAmount],t.[LoanPaymentPeriod],t.[LoanPaymentMethodID],t.[Notes]
	FROM  @LoanFeasabilityStudy_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanFeasabilityStudy_Tracking s
        WHERE t.LoanFeasabilityStudyID = s.LoanFeasabilityStudyID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanFeasabilityStudy]
	SET [LoanFeasabilityStudy].[LoanID] = t.[LoanID],[LoanFeasabilityStudy].[FeasabilityStudyDate] = t.[FeasabilityStudyDate],[LoanFeasabilityStudy].[RevenueAmount] = t.[RevenueAmount],[LoanFeasabilityStudy].[CostAmount] = t.[CostAmount],[LoanFeasabilityStudy].[ProfitAmount] = t.[ProfitAmount],[LoanFeasabilityStudy].[ReturnOnInvestmentPercent] = t.[ReturnOnInvestmentPercent],[LoanFeasabilityStudy].[LoanAmount] = t.[LoanAmount],[LoanFeasabilityStudy].[LoanPaymentPeriod] = t.[LoanPaymentPeriod],[LoanFeasabilityStudy].[LoanPaymentMethodID] = t.[LoanPaymentMethodID],[LoanFeasabilityStudy].[Notes] = t.[Notes] 
	FROM [dbo].[LoanFeasabilityStudy] s JOIN @LoanFeasabilityStudy_Temp t 
    ON t.LoanFeasabilityStudyID = s.LoanFeasabilityStudyID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanFeasabilityStudy_Tracking r
				  WHERE t.LoanFeasabilityStudyID = r.LoanFeasabilityStudyID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanFeasabilityStudy]
    FROM @LoanFeasabilityStudy_Temp t JOIN [dbo].[LoanFeasabilityStudy] s
    ON t.LoanFeasabilityStudyID = s.LoanFeasabilityStudyID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END














